<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
  <title id="cf20941">CREATE TABLESPACE</title>
  <body>
    <p id="sql_command_desc">Defines a new tablespace.</p>
    <section id="section2">
      <title>Synopsis</title>
      <codeblock id="sql_command_synopsis">CREATE TABLESPACE <varname>tablespace_name</varname> [OWNER <varname>username</varname>]  LOCATION '<varname>/path/to/dir</varname>' 
   [WITH (content<varname>ID_1</varname>='<varname>/path/to/dir1</varname>'[, content<varname>ID_2</varname>='<varname>/path/to/dir2</varname>' ... ])]</codeblock>
    </section>
    <section id="section3">
      <title>Description</title>
      <p><codeph>CREATE TABLESPACE</codeph> registers and configures a new tablespace for your
        Greenplum Database system. The tablespace name must be distinct from the name of any
        existing tablespace in the system. A tablespace is a Greenplum Database system object (a
        global object), you can use a tablespace from any database if you have appropriate
        privileges.</p>
      <p>A tablespace allows superusers to define an alternative host file system location where the
        data files containing database objects (such as tables and indexes) reside.</p>
      <p>A user with appropriate privileges can pass a tablespace name to <codeph><xref
            href="CREATE_DATABASE.xml#topic1" type="topic" format="dita"/></codeph>, <codeph><xref
            href="CREATE_TABLE.xml#topic1" type="topic" format="dita"/></codeph>, or <codeph><xref
            href="CREATE_INDEX.xml#topic1" type="topic" format="dita"/></codeph> to have the data
        files for these objects stored within the specified tablespace.</p>
      <p>In Greenplum Database, the file system location must exist on all hosts including the hosts
        running the master, standby mirror, each primary segment, and each mirror segment. </p>
    </section>
    <section id="section4">
      <title>Parameters</title>
      <parml>
        <plentry>
          <pt><varname>tablespacename</varname></pt>
          <pd>The name of a tablespace to be created. The name cannot begin with
              <codeph>pg_</codeph> or <codeph>gp_</codeph>, as such names are reserved for system
            tablespaces. </pd>
        </plentry>
        <plentry>
          <pt>OWNER <varname>username</varname></pt>
          <pd>The name of the user who will own the tablespace. If omitted, defaults to the user
            running the command. Only superusers can create tablespaces, but they can assign
            ownership of tablespaces to non-superusers. </pd>
        </plentry>
        <plentry>
          <pt>LOCATION '<varname>/path/to/dir</varname>'</pt>
          <pd>The absolute path to the directory (host system file location) that will be the root
            directory for the tablespace. When registering a tablepace, the directory should be
            empty and must be owned by the Greenplum Database system user. The directory must be
            specified by an absolute path name of no more than 100 characters. (The location is used
            to create a symlink target in the <filepath>pg_tblspc</filepath> directory, and symlink
            targets are truncated to 100 characters when sending to <codeph>tar</codeph> from
            utilities such as <codeph>pg_basebackup</codeph>.)</pd>
          <pd>For each segment instance, you can specify a different directory for the tablespace in
            the <codeph>WITH</codeph> clause.</pd>
        </plentry>
        <plentry>
          <pt>content<varname>ID_i</varname>='<varname>/path/to/dir_i</varname>'</pt>
          <pd>The value <varname>ID_i</varname> is the content ID for the segment instance.
              <varname>/path/to/dir_i</varname> is the absolute path to the host system file location
            that the segment instance uses as the root directory for the tablespace. You cannot
            specify the content ID of the master instance (<codeph>-1</codeph>). You can specify the
            same directory for multiple segments. </pd>
          <pd>If a segment instance is not listed in the <codeph>WITH</codeph> clause, Greenplum
            Database uses the directory specified in the <codeph>LOCATION</codeph> clause.</pd>
          <pd>When registering a tablepace, the directories should be empty and must be owned by the
            Greenplum Database system user. Each directory must be specified by an absolute path
            name of no more than 100 characters.</pd>
        </plentry>
      </parml>
    </section>
    <section id="section5">
      <title>Notes</title>
      <p>Tablespaces are only supported on systems that support symbolic links. </p>
      <p><codeph>CREATE TABLESPACE</codeph> cannot be run inside a transaction block.</p>
      <p>When creating tablespaces, ensure that file system locations have sufficient I/O speed and
        available disk space.</p>
      <p>
        <codeph>CREATE TABLESPACE</codeph> creates symbolic links from the
          <codeph>pg_tblspc</codeph> directory in the master and segment instance data directory to
        the directories specified in the command. </p>
      <p>The system catalog table <codeph>pg_tablespace</codeph> stores tablespace information. This
        command displays the tablespace OID values, names, and
        owner.<codeblock>SELECT oid, spcname, spcowner FROM pg_tablespace ;</codeblock></p>
      <p>The Greenplum Database built-in function
            <codeph>gp_tablespace_location(<varname>tablespace_oid</varname>)</codeph> displays the
        tablespace host system file locations for all segment instances. This command lists the
        segment database IDs and host system file locations for the tablespace with OID
          <codeph>16385</codeph>.<codeblock>SELECT * FROM gp_tablespace_location(16385) </codeblock></p>
    </section>
    <note>Greenplum Database does not support different tablespace locations for a primary-mirror
      pair with the same content ID. It is only possible to configure different locations for
      different content IDs. Do not modify symbolic links under the <codeph>pg_tblspc</codeph>
      directory so that primary-mirror pairs point to different file locations; this will lead to
      erroneous behavior.</note>
    <section id="section6">
      <title>Examples</title>
      <p>Create a new tablespace and specify the file system location for the master and all segment
        instances: </p>
      <codeblock>CREATE TABLESPACE mytblspace LOCATION '/gpdbtspc/mytestspace' ;</codeblock>
      <p>Create a new tablespace and specify a location for segment instances with content ID 0 and
        1. For the master and segment instances not listed in the <codeph>WITH</codeph> clause, the
        file system location for the tablespace is specified in the <codeph>LOCATION</codeph>
        clause. </p>
      <codeblock>CREATE TABLESPACE mytblspace LOCATION '/gpdbtspc/mytestspace' WITH (content0='/temp/mytest', content1='/temp/mytest');</codeblock>
      <p>The example specifies the same location for the two segment instances. You can a specify
        different location for each segment.</p>
    </section>
    <section id="section7">
      <title>Compatibility</title>
      <p><codeph>CREATE TABLESPACE</codeph> is a Greenplum Database extension. </p>
    </section>
    <section id="section8">
      <title>See Also</title>
      <p><codeph><xref href="CREATE_DATABASE.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="CREATE_TABLE.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="CREATE_INDEX.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="./DROP_TABLESPACE.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="ALTER_TABLESPACE.xml#topic1" type="topic" format="dita"
        /></codeph></p>
    </section>
  </body>
</topic>
